package cn.com.surker.report;

import cn.com.surker.report.bean.*;
import cn.com.surker.util.DateUtil;
import cn.com.surker.util.MathUtils;
import cn.com.surker.util.StringUtils;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.util.*;

/**
 * @Author lucky
 * @create 2021年12月16 10:36
 * @desc
 */
public class ExcelUtils {


	public static XSSFWorkbook getFaultLogWorkbook(String sheetName, String tableTitle, String type, String desc, Map<String, List<String>> listMap, XSSFWorkbook wb) {
		if (wb == null) {
			wb = new XSSFWorkbook();
		}
		XSSFSheet sheet = wb.createSheet(sheetName);
		// 设置列宽
		sheet.setColumnWidth(0, 16000);
		sheet.setColumnWidth(1, 6000);
		// 设置居中格式
		XSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		short rowHeight = 600;
		// 设置第一行数据
		XSSFRow tableTitleRow = sheet.createRow(0);
		tableTitleRow.setHeight(rowHeight);
		XSSFCell tableTitleCell = tableTitleRow.createCell(0);
		tableTitleCell.setCellValue(tableTitle);
		tableTitleCell.setCellStyle(style);
		CellRangeAddress tableTitleR = new CellRangeAddress(0, 0, 0, 1);
		sheet.addMergedRegion(tableTitleR);
		// 设置第一行标题
		XSSFRow titleRow = sheet.createRow(1);
		titleRow.setHeight(rowHeight);
		XSSFCell tcell1 = titleRow.createCell(0);
		tcell1.setCellValue(type);
		tcell1.setCellStyle(style);
		XSSFCell tcell2 = titleRow.createCell(1);
		tcell2.setCellValue(desc);
		tcell2.setCellStyle(style);
		// 设置后续数据
		Set<String> keys = listMap.keySet();
		int rowNum = 2;
		for (String key : keys) {
			// 该类型行起始
			int beginRow = rowNum;
			List<String> datas = listMap.get(key);
			for (int i = 0, s = datas.size(); i < s; i++) {
				String data = datas.get(i);
				XSSFRow row = sheet.createRow(rowNum);
				row.setHeight(rowHeight);
				XSSFCell cell0 = row.createCell(0);
				cell0.setCellValue(key);
				cell0.setCellStyle(style);
				XSSFCell cell1 = row.createCell(1);
				cell1.setCellValue(data);
				cell1.setCellStyle(style);
				rowNum++;
			}
			int endRow = rowNum - 1;
			CellRangeAddress region = new CellRangeAddress(beginRow, endRow, 0, 0);
			sheet.addMergedRegion(region);
		}
		return wb;
	}

	public static XSSFWorkbook getOutputValueHSSFWorkbook(String sheetName, String date, List<Map<String, Object>> listMap, XSSFWorkbook wb) {
		if (wb == null) {
			wb = new XSSFWorkbook();
		}
		XSSFSheet sheet = wb.createSheet(sheetName);
		// 设置第一列
		sheet.setColumnWidth(0, 16000);
		sheet.setColumnWidth(1, 6000);
		sheet.setColumnWidth(2, 6000);
		// 设置居中格式
		XSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);
		//
		XSSFRow frow = sheet.createRow(0);
		XSSFCell frowc = frow.createCell(0);
		frowc.setCellValue(sheetName);
		frowc.setCellStyle(style);
		//
		XSSFRow srow = sheet.createRow(1);
		XSSFCell srowc = srow.createCell(0);
		srowc.setCellValue("时间段：" + date);
		srowc.setCellStyle(style);
		// 设置第一行标题
		XSSFRow titleRow = sheet.createRow(2);
		XSSFCell tcell1 = titleRow.createCell(0);
		tcell1.setCellValue("工程名称");
		tcell1.setCellStyle(style);
		XSSFCell tcell2 = titleRow.createCell(1);
		tcell2.setCellValue("销售量");
		tcell2.setCellStyle(style);
		XSSFCell tcell3 = titleRow.createCell(2);
		tcell3.setCellValue("合计金额");
		tcell3.setCellStyle(style);
		XSSFCell tcell4 = titleRow.createCell(3);
		tcell4.setCellValue("客服经理");
		tcell4.setCellStyle(style);
		XSSFCell tcell5 = titleRow.createCell(4);
		tcell5.setCellValue("开票单位");
		tcell5.setCellStyle(style);
		Double totalVS = 0.0;
		Double totalReceivable = 0.0;
		for (int i = 0, s = listMap.size(); i < s; i++) {
			Map<String, Object> map = listMap.get(i);
			String cName = map.get("cName").toString();
			String kName = map.get("kName").toString();
			cName += "[" + kName + "]";
			String verifyStere = map.get("verifyStere").toString();
			try {
				Double t = Double.valueOf(verifyStere);
				totalVS += t;
				t = MathUtils.decimal(t, 2);
				verifyStere = t.toString();
			} catch (NumberFormatException e) {
				e.printStackTrace();
			}
			String receivable = map.get("receivable").toString();
			try {
				Double aDouble = Double.valueOf(receivable);
				totalReceivable += aDouble;
				aDouble = MathUtils.decimal(aDouble, 2);
				receivable = aDouble.toString();
			} catch (NumberFormatException e) {
				e.printStackTrace();
			}
			String saleMan = map.get("saleMan").toString();
			String property = map.get("property").toString();
			XSSFRow row = sheet.createRow(i + 3);
			XSSFCell cell1 = row.createCell(0);
			cell1.setCellValue(cName);
			cell1.setCellStyle(style);
			XSSFCell cell2 = row.createCell(1);
			cell2.setCellValue(verifyStere);
			cell2.setCellStyle(style);
			XSSFCell cell3 = row.createCell(2);
			cell3.setCellValue(receivable);
			cell3.setCellStyle(style);
			XSSFCell cell4 = row.createCell(3);
			cell4.setCellValue(saleMan);
			cell4.setCellStyle(style);
			XSSFCell cell5 = row.createCell(4);
			if ("1".equals(property)) {
				property = "√";
			} else {
				property = "";
			}
			cell5.setCellValue(property);
			cell5.setCellStyle(style);
		}
		totalVS = MathUtils.decimal(totalVS, 2);
		totalReceivable = MathUtils.decimal(totalReceivable, 2);
		int i = listMap.size() + 2;
		XSSFRow trow = sheet.createRow(i);
		// 合计列
		XSSFCell trowc0 = trow.createCell(0);
		trowc0.setCellValue("合计");
		trowc0.setCellStyle(style);
		// 合计方量
		XSSFCell trowc1 = trow.createCell(1);
		trowc1.setCellValue(totalVS);
		trowc1.setCellStyle(style);
		// 合计金额
		XSSFCell trowc2 = trow.createCell(2);
		trowc2.setCellValue(totalReceivable);
		trowc2.setCellStyle(style);
		// 设置合并行
		CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, 4);
		sheet.addMergedRegion(region1);
		CellRangeAddress region2 = new CellRangeAddress(1, 1, 0, 4);
		sheet.addMergedRegion(region2);
		return wb;
	}

	/**
	 * 导出Excel
	 *
	 * @param sheetName sheet名称
	 * @param wb        XSSFWorkbook对象
	 * @return
	 */
	public static XSSFWorkbook getExpHSSFWorkbook(String sheetName, String date, List<Map<String, Object>> listMap, XSSFWorkbook wb) {
		if (wb == null) {
			wb = new XSSFWorkbook();
		}
		XSSFSheet sheet = wb.createSheet(sheetName);
		sheet.setColumnWidth(0, 2800);
		sheet.setColumnWidth(1, 7800);
		sheet.setColumnWidth(2, 6600);
		sheet.setColumnWidth(3, 2600);
		sheet.setColumnWidth(4, 2600);
		sheet.setColumnWidth(15, 2600);
		sheet.setColumnWidth(16, 2600);
		sheet.setColumnWidth(7, 2600);
		sheet.setColumnWidth(8, 2200);
		sheet.setColumnWidth(9, 2200);
		sheet.setColumnWidth(10, 2200);
		sheet.setColumnWidth(11, 2600);
		sheet.setColumnWidth(12, 2200);
		sheet.setColumnWidth(13, 2200);
		sheet.setColumnWidth(14, 2200);
		sheet.setColumnWidth(5, 6400);
		sheet.setColumnWidth(6, 6400);

		//添加表头第0行
		XSSFRow titleRow = sheet.createRow(0);
		//指定行高
		titleRow.setHeight((short) 500);

		/*
		 * 这个对象中有四个参数
		 * 第一个代表：起始行
		 * 第二个代表：结束行
		 * 第三个代表：起始列
		 * 第四个代表：结束列
		 */
		CellRangeAddress cra = new CellRangeAddress(0, 1, 0, 3);
		CellRangeAddress cra1 = new CellRangeAddress(0, 1, 4, 16);
		CellRangeAddress cras1 = new CellRangeAddress(2, 3, 0, 0);
		CellRangeAddress cras2 = new CellRangeAddress(2, 3, 1, 1);
		CellRangeAddress cras3 = new CellRangeAddress(2, 3, 2, 2);
		CellRangeAddress cras4 = new CellRangeAddress(2, 3, 3, 3);
		CellRangeAddress cras5 = new CellRangeAddress(2, 3, 4, 4);
		CellRangeAddress cras6 = new CellRangeAddress(2, 3, 15, 15);
		CellRangeAddress cras7 = new CellRangeAddress(2, 3, 16, 16);
		CellRangeAddress cras8 = new CellRangeAddress(2, 3, 7, 7);
		CellRangeAddress cras9 = new CellRangeAddress(2, 2, 8, 10);
		CellRangeAddress cras10 = new CellRangeAddress(2, 3, 11, 11);
		CellRangeAddress cras11 = new CellRangeAddress(2, 2, 12, 14);
		CellRangeAddress cras12 = new CellRangeAddress(2, 3, 5, 5);
		CellRangeAddress cras13 = new CellRangeAddress(2, 3, 6, 6);
		//把合并样式添加到sheet中
		sheet.addMergedRegion(cra);
		sheet.addMergedRegion(cra1);
		sheet.addMergedRegion(cras1);
		sheet.addMergedRegion(cras2);
		sheet.addMergedRegion(cras3);
		sheet.addMergedRegion(cras4);
		sheet.addMergedRegion(cras5);
		sheet.addMergedRegion(cras6);
		sheet.addMergedRegion(cras7);
		sheet.addMergedRegion(cras8);
		sheet.addMergedRegion(cras9);
		sheet.addMergedRegion(cras10);
		sheet.addMergedRegion(cras11);
		sheet.addMergedRegion(cras12);
		sheet.addMergedRegion(cras13);

		//设置值表头 设置表头居中
		XSSFCellStyle titleStyle = wb.createCellStyle();
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
		titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		titleStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
		titleStyle.setBorderBottom(BorderStyle.THIN); //下边框
		titleStyle.setBorderLeft(BorderStyle.THIN);//左边框
		titleStyle.setBorderTop(BorderStyle.THIN);//上边框
		titleStyle.setBorderRight(BorderStyle.THIN);//右边框
		titleStyle.setFillForegroundColor(IndexedColors.WHITE.index);
		XSSFFont font = wb.createFont();
		font.setBold(true);
		font.setFontHeightInPoints((short) 24);//设置字体大小
		font.setFontName("宋体");
		font.setFontHeight((short) 650);
		titleStyle.setFont(font);

		XSSFRow titleRow1 = sheet.createRow(1);

		//声明列对象
		XSSFCell cell = titleRow.createCell(0);
		cell.setCellStyle(titleStyle);
		cell.setCellValue(sheetName + "( " + date + ")");
		cell = titleRow.createCell(3);
		cell.setCellStyle(titleStyle);
		cell = titleRow.createCell(16);
		cell.setCellStyle(titleStyle);
		cell = titleRow1.createCell(3);
		cell.setCellStyle(titleStyle);

//        titleRow1 = sheet.createRow(1);
		cell = titleRow.createCell(4);
		cell.setCellValue("混凝土抗压强度试验原始记录");
		cell.setCellStyle(titleStyle);
		cell = titleRow1.createCell(4);
		cell.setCellStyle(titleStyle);
		cell = titleRow1.createCell(8);
		cell.setCellStyle(titleStyle);
		cell = titleRow1.createCell(9);
		cell.setCellStyle(titleStyle);
		cell = titleRow1.createCell(10);
		cell.setCellStyle(titleStyle);
		cell = titleRow1.createCell(11);
		cell.setCellStyle(titleStyle);
		cell = titleRow1.createCell(13);
		cell.setCellStyle(titleStyle);
		cell = titleRow1.createCell(14);
		cell.setCellStyle(titleStyle);
		cell = titleRow1.createCell(16);
		cell.setCellStyle(titleStyle);

		/**标题行**/
		XSSFCellStyle tipStyle = wb.createCellStyle();
		tipStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
		tipStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		tipStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
		tipStyle.setBorderBottom(BorderStyle.THIN); //下边框
		tipStyle.setBorderLeft(BorderStyle.THIN);//左边框
		tipStyle.setBorderTop(BorderStyle.THIN);//上边框
		tipStyle.setBorderRight(BorderStyle.THIN);//右边框
		tipStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
		tipStyle.setWrapText(true); //设置换行
		font.setFontHeightInPoints((short) 12);//设置字体大小
		tipStyle.setFont(font);

		XSSFRow tipRow = sheet.createRow(2);
		XSSFRow tipRow1 = sheet.createRow(3);
		tipRow.setHeight((short) 500);
		cell = tipRow.createCell(0);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("试件编号"));
		cell = tipRow.createCell(1);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("工程名称"));
		cell = tipRow.createCell(2);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("浇筑部位"));
		cell = tipRow.createCell(3);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("成型日期"));
		cell = tipRow.createCell(4);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("强度等级"));
		cell = tipRow.createCell(5);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("水泥"));
		cell = tipRow.createCell(6);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("参合料"));
		cell = tipRow.createCell(7);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("凝期1"));
		cell = tipRow.createCell(8);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("抗压强度1"));
		cell = tipRow.createCell(11);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("凝期2"));
		cell = tipRow.createCell(12);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("抗压强度2"));
		cell = tipRow.createCell(15);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("试件尺寸"));
		cell = tipRow.createCell(16);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("坍落度"));

		cell = tipRow1.createCell(8);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("破坏荷载"));
		cell = tipRow1.createCell(9);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("单块值"));
		cell = tipRow1.createCell(10);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("代表值"));
		cell = tipRow1.createCell(12);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("破坏荷载"));
		cell = tipRow1.createCell(13);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("单块值"));
		cell = tipRow1.createCell(14);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("代表值"));


		cell = tipRow1.createCell(0);
		cell.setCellStyle(tipStyle);
		cell = tipRow1.createCell(1);
		cell.setCellStyle(tipStyle);
		cell = tipRow1.createCell(2);
		cell.setCellStyle(tipStyle);
		cell = tipRow1.createCell(3);
		cell.setCellStyle(tipStyle);
		cell = tipRow1.createCell(4);
		cell.setCellStyle(tipStyle);
		cell = tipRow1.createCell(5);
		cell.setCellStyle(tipStyle);
		cell = tipRow1.createCell(6);
		cell.setCellStyle(tipStyle);
		cell = tipRow1.createCell(7);
		cell.setCellStyle(tipStyle);
		cell = tipRow1.createCell(11);
		cell.setCellStyle(tipStyle);
		cell = tipRow1.createCell(15);
		cell.setCellStyle(tipStyle);
		cell = tipRow1.createCell(16);
		cell.setCellStyle(tipStyle);

		Integer index = 4;

		XSSFCellStyle valueStyle = wb.createCellStyle();
		valueStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
		valueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		valueStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
		valueStyle.setBorderBottom(BorderStyle.THIN); //下边框
		valueStyle.setBorderLeft(BorderStyle.THIN);//左边框
		valueStyle.setWrapText(true); //设置换行
		valueStyle.setBorderTop(BorderStyle.THIN);//上边框
		valueStyle.setBorderRight(BorderStyle.THIN);//右边框
		valueStyle.setFillForegroundColor(IndexedColors.WHITE.index);

		for (int i = 0; i < listMap.size(); i++) {
			/**跨行合并**/
			cras1 = new CellRangeAddress(index, index + 2, 0, 0);
			cras2 = new CellRangeAddress(index, index + 2, 1, 1);
			cras3 = new CellRangeAddress(index, index + 2, 2, 2);
			cras4 = new CellRangeAddress(index, index + 2, 3, 3);
			cras5 = new CellRangeAddress(index, index + 2, 4, 4);
			cras6 = new CellRangeAddress(index, index + 2, 5, 5);
			cras7 = new CellRangeAddress(index, index + 2, 6, 6);
			cras8 = new CellRangeAddress(index, index + 2, 7, 7);
			cras9 = new CellRangeAddress(index, index + 2, 10, 10);
			cras10 = new CellRangeAddress(index, index + 2, 11, 11);
			cras11 = new CellRangeAddress(index, index + 2, 14, 14);
			cras12 = new CellRangeAddress(index, index + 2, 15, 15);
			cras13 = new CellRangeAddress(index, index + 2, 16, 16);

			sheet.addMergedRegion(cras1);
			sheet.addMergedRegion(cras2);
			sheet.addMergedRegion(cras3);
			sheet.addMergedRegion(cras4);
			sheet.addMergedRegion(cras5);
			sheet.addMergedRegion(cras6);
			sheet.addMergedRegion(cras7);
			sheet.addMergedRegion(cras8);
			sheet.addMergedRegion(cras9);
			sheet.addMergedRegion(cras10);
			sheet.addMergedRegion(cras11);
			sheet.addMergedRegion(cras12);
			sheet.addMergedRegion(cras13);
			XSSFRow row = sheet.createRow(index);
			XSSFRow row1 = sheet.createRow(index + 1);
			XSSFRow row2 = sheet.createRow(index + 2);
			Map<String, Object> map = listMap.get(i);

			cell = row.createCell(0);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(map.get("exp_code"))));
			cell.setCellStyle(valueStyle);
			cell = row.createCell(1);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(map.get("project"))));
			cell.setCellStyle(valueStyle);
			cell = row.createCell(2);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(map.get("site"))));
			cell.setCellStyle(valueStyle);
			cell = row.createCell(3);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(map.get("proTime"))));
			cell.setCellStyle(valueStyle);
			cell = row.createCell(4);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(map.get("level"))));
			cell.setCellStyle(valueStyle);
			cell = row.createCell(15);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(map.get("size"))));
			cell.setCellStyle(valueStyle);
			cell = row.createCell(16);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(map.get("slumps"))));
			cell.setCellStyle(valueStyle);
			cell = row.createCell(7);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(map.get("comTime1"))));
			cell.setCellStyle(valueStyle);
			cell = row.createCell(10);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(map.get("com1Value"))));
			cell.setCellStyle(valueStyle);
			cell = row.createCell(11);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(map.get("comTime2"))));
			cell.setCellStyle(valueStyle);
			cell = row.createCell(14);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(map.get("com2Value"))));
			cell.setCellStyle(valueStyle);
			cell = row.createCell(5);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(map.get("material1"))));
			cell.setCellStyle(valueStyle);
			cell = row.createCell(6);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(map.get("material2"))));
			cell.setCellStyle(valueStyle);
			List<Map<String, Object>> data1 = (List<Map<String, Object>>) map.get("comData1");
			List<Map<String, Object>> data2 = (List<Map<String, Object>>) map.get("comData2");
			cell = row.createCell(8);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(data1.get(0).get("load"))));
			cell.setCellStyle(valueStyle);

			cell = row.createCell(9);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(data1.get(0).get("value"))));
			cell.setCellStyle(valueStyle);

			cell = row1.createCell(8);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(data1.get(1).get("load"))));
			cell.setCellStyle(valueStyle);

			cell = row1.createCell(9);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(data1.get(1).get("value"))));
			cell.setCellStyle(valueStyle);

			cell = row2.createCell(8);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(data1.get(2).get("load"))));
			cell.setCellStyle(valueStyle);

			cell = row2.createCell(9);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(data1.get(2).get("value"))));
			cell.setCellStyle(valueStyle);


			cell = row.createCell(12);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(data2.get(0).get("load"))));
			cell.setCellStyle(valueStyle);

			cell = row.createCell(13);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(data2.get(0).get("value"))));
			cell.setCellStyle(valueStyle);

			cell = row1.createCell(12);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(data2.get(1).get("load"))));
			cell.setCellStyle(valueStyle);

			cell = row1.createCell(13);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(data2.get(1).get("value"))));
			cell.setCellStyle(valueStyle);

			cell = row2.createCell(12);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(data2.get(2).get("load"))));
			cell.setCellStyle(valueStyle);

			cell = row2.createCell(13);
			cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(data2.get(2).get("value"))));
			cell.setCellStyle(valueStyle);

			cell = row1.createCell(0);
			cell.setCellStyle(valueStyle);
			cell = row1.createCell(1);
			cell.setCellStyle(valueStyle);
			cell = row1.createCell(2);
			cell.setCellStyle(valueStyle);
			cell = row1.createCell(3);
			cell.setCellStyle(valueStyle);
			cell = row1.createCell(4);
			cell.setCellStyle(valueStyle);
			cell = row1.createCell(5);
			cell.setCellStyle(valueStyle);
			cell = row1.createCell(6);
			cell.setCellStyle(valueStyle);
			cell = row1.createCell(7);
			cell.setCellStyle(valueStyle);
			cell = row1.createCell(10);
			cell.setCellStyle(valueStyle);
			cell = row1.createCell(11);
			cell.setCellStyle(valueStyle);
			cell = row1.createCell(14);
			cell.setCellStyle(valueStyle);
			cell = row1.createCell(15);
			cell.setCellStyle(valueStyle);
			cell = row1.createCell(16);
			cell.setCellStyle(valueStyle);

			cell = row2.createCell(0);
			cell.setCellStyle(valueStyle);
			cell = row2.createCell(1);
			cell.setCellStyle(valueStyle);
			cell = row2.createCell(2);
			cell.setCellStyle(valueStyle);
			cell = row2.createCell(3);
			cell.setCellStyle(valueStyle);
			cell = row2.createCell(4);
			cell.setCellStyle(valueStyle);
			cell = row2.createCell(5);
			cell.setCellStyle(valueStyle);
			cell = row2.createCell(6);
			cell.setCellStyle(valueStyle);
			cell = row2.createCell(7);
			cell.setCellStyle(valueStyle);
			cell = row2.createCell(10);
			cell.setCellStyle(valueStyle);
			cell = row2.createCell(11);
			cell.setCellStyle(valueStyle);
			cell = row2.createCell(14);
			cell.setCellStyle(valueStyle);
			cell = row2.createCell(15);
			cell.setCellStyle(valueStyle);
			cell = row2.createCell(16);
			cell.setCellStyle(valueStyle);
			index += 3;
		}

		return wb;
	}

	/**
	 * 导出Excel
	 *
	 * @param sheetName sheet名称
	 * @param wb        XSSFWorkbook对象
	 * @return
	 */
	public static XSSFWorkbook getHSSFWorkbook(String sheetName, String dsName, String date, Map<String, List<Map<String, Object>>> map, XSSFWorkbook wb) {
		if (wb == null) {
			wb = new XSSFWorkbook();
		}
		XSSFSheet sheet = wb.createSheet(sheetName);
		sheet.setColumnWidth(0, 8600);
		sheet.setColumnWidth(1, 7400);
		sheet.setColumnWidth(2, 6900);
		sheet.setColumnWidth(3, 5900);
		sheet.setColumnWidth(4, 5900);
		sheet.setColumnWidth(5, 5900);
		sheet.setColumnWidth(6, 5900);

		//添加表头第0行
		XSSFRow titleRow = sheet.createRow(0);
		//指定行高
		titleRow.setHeight((short) 700);

		/*
		 * 这个对象中有四个参数
		 * 第一个代表：起始行
		 * 第二个代表：结束行
		 * 第三个代表：起始列
		 * 第四个代表：结束列
		 */
		CellRangeAddress cra = new CellRangeAddress(0, 1, 0, 6);
		//把合并样式添加到sheet中
		sheet.addMergedRegion(cra);

		//设置值表头 设置表头居中
		XSSFCellStyle titleStyle = wb.createCellStyle();
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
		titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		titleStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
		titleStyle.setBorderBottom(BorderStyle.THIN); //下边框
		titleStyle.setBorderLeft(BorderStyle.THIN);//左边框
		titleStyle.setBorderTop(BorderStyle.THIN);//上边框
		titleStyle.setBorderRight(BorderStyle.THIN);//右边框
		titleStyle.setFillForegroundColor(IndexedColors.WHITE.index);
		XSSFFont font = wb.createFont();
		font.setBold(true);
		font.setFontHeightInPoints((short) 24);//设置字体大小
		font.setFontName("宋体");
		font.setFontHeight((short) 650);
		titleStyle.setFont(font);

		//声明列对象
		XSSFCell cell = titleRow.createCell(0);
		cell.setCellStyle(titleStyle);
		cell.setCellValue(dsName + "公司产量日报表");
		cell = titleRow.createCell(6);
		cell.setCellStyle(titleStyle);
		titleRow = sheet.createRow(1);
		cell = titleRow.createCell(6);
		cell.setCellStyle(titleStyle);

		/**日期行**/
		XSSFRow dateRow = sheet.createRow(2);
		cell = dateRow.createCell(0);
		font.setFontHeightInPoints((short) 12);//设置字体大小
		cell.setCellStyle(titleStyle);
		dateRow.setHeight((short) 500);
		cell.setCellValue("日期");

		cell = dateRow.createCell(1);
		font.setFontHeightInPoints((short) 12);//设置字体大小
		cell.setCellStyle(titleStyle);
		cell.setCellValue(date);
		cell = dateRow.createCell(2);
		font.setFontHeightInPoints((short) 12);//设置字体大小
		cell.setCellStyle(titleStyle);
		cell = dateRow.createCell(3);
		font.setFontHeightInPoints((short) 12);//设置字体大小
		cell.setCellStyle(titleStyle);
		cell = dateRow.createCell(4);
		font.setFontHeightInPoints((short) 12);//设置字体大小
		cell.setCellStyle(titleStyle);
		cell = dateRow.createCell(5);
		font.setFontHeightInPoints((short) 12);//设置字体大小
		cell.setCellStyle(titleStyle);
		cell = dateRow.createCell(6);
		font.setFontHeightInPoints((short) 12);//设置字体大小
		cell.setCellStyle(titleStyle);


		/**标题行**/
		XSSFCellStyle tipStyle = wb.createCellStyle();
		tipStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
		tipStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		tipStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
		tipStyle.setBorderBottom(BorderStyle.THIN); //下边框
		tipStyle.setBorderLeft(BorderStyle.THIN);//左边框
		tipStyle.setBorderTop(BorderStyle.THIN);//上边框
		tipStyle.setBorderRight(BorderStyle.THIN);//右边框
		tipStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
		font.setFontHeightInPoints((short) 16);//设置字体大小
		tipStyle.setFont(font);

		XSSFRow tipRow = sheet.createRow(3);
		tipRow.setHeight((short) 500);
		cell = tipRow.createCell(0);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("产品种类"));
		cell = tipRow.createCell(1);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("客户单位"));
		cell = tipRow.createCell(2);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("车牌号"));
		cell = tipRow.createCell(3);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("毛重(吨)"));
		cell = tipRow.createCell(4);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("皮重(吨)"));
		cell = tipRow.createCell(5);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("净重(吨)"));
		cell = tipRow.createCell(6);
		cell.setCellStyle(tipStyle);
		cell.setCellValue(new HSSFRichTextString("车数"));

		Integer index = 4;
		Double sumGross = 0.0;
		Double sumTare = 0.0;
		Double sumNetWeight = 0.0;
		Integer sumTotal = 0;

		XSSFCellStyle countStyle = wb.createCellStyle();
		countStyle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式// 单元格字体//设置填充样式
		countStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		countStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
		countStyle.setBorderBottom(BorderStyle.THIN); //下边框
		countStyle.setBorderLeft(BorderStyle.THIN);//左边框
		countStyle.setBorderTop(BorderStyle.THIN);//上边框
		countStyle.setBorderRight(BorderStyle.THIN);//右边框
		countStyle.setFillForegroundColor(IndexedColors.WHITE.index);
		font.setFontHeightInPoints((short) 16);//设置字体大小
		countStyle.setFont(font);

		XSSFCellStyle valueStyle = wb.createCellStyle();
		valueStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
		valueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		valueStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
		valueStyle.setBorderBottom(BorderStyle.THIN); //下边框
		valueStyle.setBorderLeft(BorderStyle.THIN);//左边框
		valueStyle.setBorderTop(BorderStyle.THIN);//上边框
		valueStyle.setBorderRight(BorderStyle.THIN);//右边框
		valueStyle.setFillForegroundColor(IndexedColors.WHITE.index);

		for (String key : map.keySet()) {
			Double gross = 0.0;
			Double tare = 0.0;
			Double netWeight = 0.0;
			Integer total = 0;
			for (int i = 0; i < map.get(key).size(); i++) {
				Map<String, Object> temp = map.get(key).get(i);
				XSSFRow row = sheet.createRow(index);
				row.setHeight((short) 500);
				Double tempGross = StringUtils.formatDouble(StringUtils.convertDouble(temp.get("gross")), 2);
				Double tempTare = StringUtils.formatDouble(StringUtils.convertDouble(temp.get("tare")), 2);
				Double tempNetWeight = StringUtils.formatDouble(StringUtils.convertDouble(temp.get("netWeight")), 2);
				Integer tempTotal = StringUtils.convertInteger(temp.get("total"));
				gross += tempGross;
				tare += tempTare;
				netWeight += tempNetWeight;
				total += tempTotal;
				cell = row.createCell(0);
				cell.setCellValue(new HSSFRichTextString(key));
				cell.setCellStyle(valueStyle);
				cell = row.createCell(1);
				cell.setCellStyle(valueStyle);
				cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(temp.get("ClientName"))));
				cell = row.createCell(2);
				cell.setCellStyle(valueStyle);
				cell.setCellValue(new HSSFRichTextString(StringUtils.convertString(temp.get("license"))));
				cell = row.createCell(3);
				cell.setCellStyle(valueStyle);
				cell.setCellValue(tempGross);
				cell = row.createCell(4);
				cell.setCellStyle(valueStyle);
				cell.setCellValue(tempTare);
				cell = row.createCell(5);
				cell.setCellStyle(valueStyle);
				cell.setCellValue(tempNetWeight);
				cell = row.createCell(6);
				cell.setCellStyle(valueStyle);
				cell.setCellValue(tempTotal);
				index++;
			}
			cra = new CellRangeAddress(index - map.get(key).size(), index, 0, 0);
			//把合并样式添加到sheet中
			sheet.addMergedRegion(cra);

			XSSFRow countRow = sheet.createRow(index);
			countRow.setHeight((short) 500);
			cell = countRow.createCell(0);
			cell.setCellStyle(countStyle);
			cell.setCellValue("");
			cell = countRow.createCell(1);
			cell.setCellStyle(countStyle);
			cell.setCellValue("小计");
			cell = countRow.createCell(2);
			cell.setCellStyle(countStyle);
			cell.setCellValue("");
			cell = countRow.createCell(3);
			cell.setCellStyle(countStyle);
			cell.setCellValue(gross);
			cell = countRow.createCell(4);
			cell.setCellStyle(countStyle);
			cell.setCellValue(tare);
			cell = countRow.createCell(5);
			cell.setCellStyle(countStyle);
			cell.setCellValue(netWeight);
			cell = countRow.createCell(6);
			cell.setCellStyle(countStyle);
			cell.setCellValue(total);
			index++;

			sumGross += gross;
			sumTare += tare;
			sumNetWeight += netWeight;
			sumTotal += total;
		}

		XSSFRow countRow = sheet.createRow(index);
		cell = countRow.createCell(0);
		cell.setCellStyle(countStyle);
		cell.setCellValue("");
		cell = countRow.createCell(1);
		cell.setCellStyle(countStyle);
		cell.setCellValue("合计");
		cell = countRow.createCell(2);
		cell.setCellStyle(countStyle);
		cell.setCellValue("");
		cell = countRow.createCell(3);
		cell.setCellStyle(countStyle);
		cell.setCellValue(sumGross);
		cell = countRow.createCell(4);
		cell.setCellStyle(countStyle);
		cell.setCellValue(sumTare);
		cell = countRow.createCell(5);
		cell.setCellStyle(countStyle);
		cell.setCellValue(sumNetWeight);
		cell = countRow.createCell(6);
		cell.setCellStyle(countStyle);
		cell.setCellValue(sumTotal);

		return wb;
	}

	/**
	 * 考勤记录
	 *
	 * @param affirmYear   统计年
	 * @param excelName    表名
	 * @param signNamePath 签名路径
	 * @param lists        考勤数据
	 * @param wb
	 * @return
	 */
	public static XSSFWorkbook clockInWorkbook(String affirmYear, String excelName, String signNamePath, String remarks, List<List> lists, XSSFWorkbook wb) throws IOException {
		if (wb == null) {
			wb = new XSSFWorkbook();
			return wb;
		}
		if (lists == null || lists.isEmpty()) {
			// 如果为空，返回空数据
			return wb;
		}
		// 创建分区数据
		XSSFSheet sheet = wb.createSheet(excelName);
		// 设置列宽
		//	序号列
		sheet.setColumnWidth(0, 1200);
		//	姓名列
		sheet.setColumnWidth(1, 2100);
		//	岗位列
		sheet.setColumnWidth(2, 3200);
		//	数据列
		for (int i = 3; i < 40; i++) {
			sheet.setColumnWidth(3, 2000);
		}
		// 标题样式
		XSSFCellStyle titleStyle = wb.createCellStyle();
		// 设置居中样式
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		titleStyle.setAlignment(HorizontalAlignment.CENTER);
		//	设置字体
		XSSFFont titleFont = wb.createFont();
		titleFont.setBold(true);
		titleFont.setFontHeightInPoints((short) 18);//设置字体大小
		titleFont.setFontName("微软雅黑");
		titleStyle.setFont(titleFont);
		// 设置标题数据
		XSSFRow titleRow = sheet.createRow(0);
		titleRow.setHeight((short) 1200);
		XSSFCell orgRowCell = titleRow.createCell(0);
		// 设置边框
		titleStyle.setBorderBottom(BorderStyle.THIN); //下边框
		titleStyle.setBorderLeft(BorderStyle.THIN);//左边框
		titleStyle.setBorderTop(BorderStyle.THIN);//上边框
		titleStyle.setBorderRight(BorderStyle.THIN);//右边框
		orgRowCell.setCellStyle(titleStyle);
		orgRowCell.setCellValue(excelName);
		// 合并单元格
		CellRangeAddress region = new CellRangeAddress(0, 0, 0, lists.get(0).size() - 1);
		sheet.addMergedRegion(region);
		//	设置周末
		List days = lists.get(0);
		//	统计日期
		Date affirmDate = null;
		Calendar c = Calendar.getInstance();
		List<String> weeks = new ArrayList<>();
		for (int i = 0, s = days.size(); i < s; i++) {
			Object o = days.get(i);
			if (o == null) {
				weeks.add("-");
				continue;
			}
			String weekStr = String.valueOf(o);
			if (affirmDate != null) {
				//	之前有数据
				int day = c.get(Calendar.DAY_OF_MONTH);
				c.set(Calendar.DAY_OF_MONTH, day + 1);
				affirmDate = c.getTime();
				String weekOfDate = DateUtil.getWeekOfDate(affirmDate);
				weeks.add(weekOfDate);
				c.setTime(affirmDate);
				continue;
			}
			if (StringUtils.validatorDate(weekStr, "MM/dd")) {
				// 是日期格式
				Date date = DateUtil.convertDate(weekStr, "MM/dd");
				c.setTime(date);
				c.set(Calendar.YEAR, Integer.valueOf(affirmYear));
				affirmDate = c.getTime();
				String weekOfDate = DateUtil.getWeekOfDate(affirmDate);
				weeks.add(weekOfDate);
			} else {
				//	不是日期格式
				weeks.add("-");
			}
		}
		lists.add(1, weeks);
		// 设置格式
//        XSSFPalette customPalette = wb.getCustomPalette();
		//	填充数据
		for (int i = 0, s = lists.size(); i < s; i++) {
			// 创建数据行
			XSSFRow sheetRow = sheet.createRow(i + 1);
			// 设置行高
			sheetRow.setHeight((short) 500);
			List list = lists.get(i);
			for (int a = 0, b = list.size(); a < b; a++) {
				// 创建数据列
				XSSFCell cell = sheetRow.createCell(a);
				Object o = list.get(a);
				XSSFCellStyle cellStyle = wb.createCellStyle();
				cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
				cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
				cellStyle.setBorderTop(BorderStyle.THIN);//上边框
				cellStyle.setBorderRight(BorderStyle.THIN);//右边框
				// 设置居中样式
				cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
				cellStyle.setAlignment(HorizontalAlignment.CENTER);
				if (o != null) {
					if (o instanceof String) {
						cell.setCellStyle(cellStyle);
						cell.setCellValue(String.valueOf(o));
					} else if (o instanceof JSONObject) {
						String os = JSONObject.toJSONString(o);
						JSONObject jsonObject = JSONObject.parseObject(os);
						String icon = jsonObject.getString("icon");
						String color = jsonObject.getString("color");
						cell.setCellValue(icon);
						// 格式化颜色数据
						String[] colors = color.split(",");
						String red = colors[0];
						red = red.substring(red.indexOf("(") + 1);
						String green = colors[1];
						String blue = colors[2];
						blue = blue.substring(0, blue.indexOf(")"));
//                        XSSFColor similarColor = new XSSFColor(new java.awt.Color(Integer.valueOf(red), Integer.valueOf(green), Integer.valueOf(blue)), new DefaultIndexedColorMap());
//						short index = similarColor.getIndex();
						XSSFFont font = wb.createFont();
						font.setFontHeightInPoints((short) 12);
						font.setColor(new XSSFColor(new java.awt.Color(Integer.valueOf(red), Integer.valueOf(green), Integer.valueOf(blue)), null));
						cellStyle.setFont(font);
						cell.setCellStyle(cellStyle);
					} else {
						cell.setCellStyle(cellStyle);
						cell.setCellValue(String.valueOf(o));
					}
				} else {
					cell.setCellStyle(cellStyle);
					cell.setCellValue("");
				}
			}
		}
		//	设置备注行
		int remarkIndex = lists.size() + 2;
		XSSFRow remarkRow = sheet.createRow(remarkIndex);
		remarkRow.setHeight((short) 500);
		//	备注单元格
		XSSFCell remarkTitleCell = remarkRow.createCell(1);
		remarkTitleCell.setCellValue("备注:");
		//	数据单元格
		if (StringUtils.isNotBlank(remarks)) {
			XSSFCell remarkValueCell = remarkRow.createCell(2);
			remarkValueCell.setCellValue(remarks);
		}
		//	设置数据
		if (StringUtils.isNotBlank(signNamePath)) {
			//	图片后缀
			String suffix = "png";
			File imgFile = new File(signNamePath);
			if (imgFile.exists()) {
				XSSFCellStyle cellStyle = wb.createCellStyle();
				// 设置居中样式
				cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
				cellStyle.setAlignment(HorizontalAlignment.CENTER);
				// 获取图片行
				int imgIndex = lists.size() + 4;
				XSSFRow imgRow = sheet.createRow(imgIndex);
				imgRow.setHeight((short) 600);
				//	设置部门负责人字样
				XSSFCell imgRowCell = imgRow.createCell(1);
				imgRowCell.setCellStyle(cellStyle);
				imgRowCell.setCellValue("部门负责人");
				//	导出签名图片
				BufferedImage bufferImg = ImageIO.read(imgFile);
				ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
				ImageIO.write(bufferImg, suffix, byteArrayOut);
				XSSFDrawing patriarch = sheet.createDrawingPatriarch();
				// anchor主要用于设置图片的属性
				//    * 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
				XSSFClientAnchor anchor =
					new XSSFClientAnchor
						(0,    //x缩放
							0,        // y缩放
							1023,        //最大1023
							255,        //最大255
							(short) 3,    //从第几列开始
							imgIndex,        //在第几行
							(short) 4,        //截止列
							imgIndex        //第几列
						);
				patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
			}
		}
		return wb;
	}

	public static XSSFWorkbook clockInWorkbook2(String affirmYear, String excelName, String signNamePath, String remarks, List<List<Object>> lists, XSSFWorkbook wb) throws IOException {
		if (wb == null) {
			wb = new XSSFWorkbook();
			return wb;
		}
		if (lists == null || lists.isEmpty()) {
			// 如果为空，返回空数据
			return wb;
		}
		// 创建分区数据
		XSSFSheet sheet = wb.createSheet(excelName);
		// 设置列宽
		//	序号列
		sheet.setColumnWidth(0, 4000);
		//	姓名列
		sheet.setColumnWidth(1, 4000);
		//	岗位列
		sheet.setColumnWidth(2, 4000);
		sheet.setColumnWidth(3, 4000);
		//	数据列
		for (int i = 4; i < 120; i++) {
			sheet.setColumnWidth(i, 3500);
		}
		// 标题样式
		XSSFCellStyle titleStyle = wb.createCellStyle();
		// 设置居中样式
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		titleStyle.setAlignment(HorizontalAlignment.CENTER);
		//	设置字体
		XSSFFont titleFont = wb.createFont();
		titleFont.setBold(true);
		titleFont.setFontHeightInPoints((short) 18);//设置字体大小
		titleFont.setFontName("微软雅黑");
		titleStyle.setFont(titleFont);
		// 设置标题数据
		XSSFRow titleRow = sheet.createRow(0);
		titleRow.setHeight((short) 600);
		XSSFCell orgRowCell = titleRow.createCell(0);
		// 设置边框
		titleStyle.setBorderBottom(BorderStyle.THIN); //下边框
		titleStyle.setBorderLeft(BorderStyle.THIN);//左边框
		titleStyle.setBorderTop(BorderStyle.THIN);//上边框
		titleStyle.setBorderRight(BorderStyle.THIN);//右边框
		orgRowCell.setCellStyle(titleStyle);
		orgRowCell.setCellValue(excelName);
		// 合并单元格
		CellRangeAddress region = new CellRangeAddress(0, 0, 0, lists.get(0).size() - 1);
		sheet.addMergedRegion(region);
		//	设置周末
		List days = lists.get(0);
		//	统计日期
		Date affirmDate = null;
		Calendar c = Calendar.getInstance();
		/*List<Object> weeks = new ArrayList<>();
		for (int i = 0,s = days.size();i < s;i++){
			Object o = days.get(i);
			if (o == null){
				weeks.add("-");
				continue;
			}
			String weekStr = String.valueOf(o);
			if(affirmDate != null){
				//	之前有数据
				int day = c.get(Calendar.DAY_OF_MONTH);
				c.set(Calendar.DAY_OF_MONTH,day + 1);
				affirmDate = c.getTime();
				String weekOfDate = DateUtil.getWeekOfDate(affirmDate);
				weeks.add(weekOfDate);
				c.setTime(affirmDate);
				continue;
			}
			if(StringUtils.validatorDate(weekStr, "MM/dd")){
				// 是日期格式
				Date date = DateUtil.convertDate(weekStr, "MM/dd");
				c.setTime(date);
				c.set(Calendar.YEAR,Integer.valueOf(affirmYear));
				affirmDate = c.getTime();
				String weekOfDate = DateUtil.getWeekOfDate(affirmDate);
				weeks.add(weekOfDate);
			}else {
				//	不是日期格式
				weeks.add("-");
			}
		}*/
		// lists.add(1,weeks);
		// 设置格式
//		HSSFPalette customPalette = wb.getCustomPalette();
		//	默认样式
		XSSFCellStyle defaultStyle = wb.createCellStyle();
		defaultStyle.setBorderBottom(BorderStyle.THIN); //下边框
		defaultStyle.setBorderLeft(BorderStyle.THIN);//左边框
		defaultStyle.setBorderTop(BorderStyle.THIN);//上边框
		defaultStyle.setBorderRight(BorderStyle.THIN);//右边框
		// 设置居中样式
		defaultStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		defaultStyle.setAlignment(HorizontalAlignment.CENTER);
		//	字体样式
		XSSFCellStyle cellStyle2 = wb.createCellStyle();
		cellStyle2.setBorderBottom(BorderStyle.THIN); //下边框
		cellStyle2.setBorderLeft(BorderStyle.THIN);//左边框
		cellStyle2.setBorderTop(BorderStyle.THIN);//上边框
		cellStyle2.setBorderRight(BorderStyle.THIN);//右边框
		// 设置居中样式
		cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
		cellStyle2.setAlignment(HorizontalAlignment.CENTER);
		XSSFFont font = wb.createFont();
		font.setFontHeightInPoints((short) 12);
		// font.setColor(index);
		//	填充数据
		for (int i = 0, s = lists.size(); i < s; i++) {
			// 创建数据行
			XSSFRow sheetRow = sheet.createRow(i + 1);
			// 设置行高
			sheetRow.setHeight((short) 500);
			List list = lists.get(i);
			for (int a = 0, b = list.size(); a < b; a++) {
				// 创建数据列
				XSSFCell cell = sheetRow.createCell(a);
				Object o = list.get(a);

				if (o != null) {
					if (o instanceof String) {
						cell.setCellStyle(defaultStyle);
						cell.setCellValue(String.valueOf(o));
					} else if (o instanceof Map) {
						Map<String, Object> map = (Map<String, Object>) o;
						String icon = map.get("description").toString();
						String color = map.get("color").toString();
						cell.setCellValue(icon);
						// 格式化颜色数据
						String[] colors = color.split(",");
						String red = colors[0];
						red = red.substring(red.indexOf("(") + 1);
						String green = colors[1];
						String blue = colors[2];
						blue = blue.substring(0, blue.indexOf(")"));
//                        XSSFColor similarColor = new XSSFColor(new java.awt.Color(Integer.valueOf(red), Integer.valueOf(green), Integer.valueOf(blue)), new DefaultIndexedColorMap());
//						short index = similarColor.getIndex();
						font.setColor(new XSSFColor(new java.awt.Color(Integer.valueOf(red), Integer.valueOf(green), Integer.valueOf(blue)), null));
						cellStyle2.setFont(font);
						cell.setCellStyle(cellStyle2);
					} else {
						cell.setCellStyle(defaultStyle);
						cell.setCellValue(String.valueOf(o));
					}
				} else {
					cell.setCellStyle(defaultStyle);
					cell.setCellValue("");
				}
			}
		}
		CellRangeAddress cra = CellRangeAddress.valueOf("A2:C2");//筛选列
		sheet.setAutoFilter(cra);
		return wb;
	}


	/**
	 * 创建excel表数据
	 *
	 * @param sheets
	 * @return
	 */
	public static XSSFWorkbook baseExport(List<ExcelSheet> sheets, XSSFWorkbook wb) {
		if (wb == null) {
			return null;
		}
		XSSFDataFormat df = wb.createDataFormat();
		//	日期样式
		XSSFCellStyle dateStyle = null;
		/*cell.setCellValue(format.parse(cellValue.toString()));
		cell.setCellStyle(hssfCellStyleDate);*/
		Map<Integer, XSSFCellStyle> cellStyleMap = new HashMap<>();
		for (ExcelSheet excelSheet : sheets) {
			//	工作薄名
			String sheetName = excelSheet.getSheetName();
			if (StringUtils.isNull(sheetName))
				sheetName = "未知销售";
			int rowNum = 0;
			XSSFSheet sheet = wb.createSheet(sheetName);
			//	表名
			String tableName = excelSheet.getTableName();
			//	首行创建标题
			XSSFRow titleRow = sheet.createRow(rowNum);
			rowNum++;
			titleRow.setHeight(excelSheet.getTableHeight());
			XSSFCell titleRowCell = titleRow.createCell(0);
			//	设置表明
			titleRowCell.setCellValue(tableName);
			ExcelStyle tableStyle = excelSheet.getTableStyle();
			if (tableStyle != null) {
				XSSFCellStyle xssfTableStyle = initStyle(wb, tableStyle);
				titleRowCell.setCellStyle(xssfTableStyle);
			}
			//	填充数据
			List<ExcelRow> rows = excelSheet.getRows();
			for (ExcelRow row : rows) {
				//	创建数据行
				XSSFRow dataRow = sheet.createRow(rowNum);
				dataRow.setHeight(row.getHeight());
				//	填充数据列
				List<ExcelCell> cells = row.getCells();
				int cellNum = 0;
				for (ExcelCell cell : cells) {
					//	数据列
					XSSFCell dataCell = dataRow.createCell(cellNum);
					Object cellValue = cell.getValue();
					//	是否需要重新设置样式
					Boolean againStyle = true;
					if (cellValue instanceof Double) {
						dataCell.setCellValue(Double.valueOf(cellValue.toString()));
					} else {
						String s = String.valueOf(cellValue);
						if (StringUtils.validatorDate(s, DateUtil.DATE_ABBR_FORMATTER)) {
							//	判断是否日期格式
							Date parse = DateUtil.convertDate(s, DateUtil.DATE_ABBR_FORMATTER);
							dataCell.setCellValue(parse);
							//	设置日期样式
							if (dateStyle == null) {
								dateStyle = wb.createCellStyle();
								ExcelStyle cellStyle = cell.getCellStyle();
								if (cellStyle != null) {
									XSSFCellStyle xssfCellStyle = initStyle(wb, cellStyle);
									dateStyle.cloneStyleFrom(xssfCellStyle);
								}
								dateStyle.setDataFormat(df.getFormat(DateUtil.DATE_ABBR_FORMATTER));
							}
							dataCell.setCellStyle(dateStyle);
							againStyle = false;
						} else {
							dataCell.setCellValue(s);
						}
					}
					if (againStyle) {
						//	设置样式
						ExcelStyle cellStyle = cell.getCellStyle();
						if (cellStyle != null) {
							//	获取hash
							int cellStyleHash = cellStyle.hashCode();
							XSSFCellStyle xssfCellStyle = cellStyleMap.get(cellStyleHash);
							if (xssfCellStyle == null) {
								//	新生成
								xssfCellStyle = initStyle(wb, cellStyle);
								cellStyleMap.put(cellStyleHash, xssfCellStyle);
							}
							dataCell.setCellStyle(xssfCellStyle);
						}
					}
					cellNum++;
				}
				rowNum++;
			}
			//	设置列宽
			List<ExcelCellWidth> widths = excelSheet.getWidths();
			if (widths != null) {
				for (ExcelCellWidth cellWidth : widths) {
					sheet.setColumnWidth(cellWidth.getCellNum(), cellWidth.getWidth());
				}
			}
			// 合并行列
			sheet.addMergedRegion(new CellRangeAddress(excelSheet.getTableNameFirstRow(), excelSheet.getTableNameEndRow(), 0, excelSheet.getTableNameEndCell()));
			//	设置筛选
			String rangeAddress = excelSheet.getRangeAddress();
			if (StringUtils.isNotBlank(rangeAddress)) {
				CellRangeAddress c = CellRangeAddress.valueOf(rangeAddress);//筛选列
				sheet.setAutoFilter(c);
			}
		}
		return wb;
	}

	private static XSSFCellStyle initStyle(XSSFWorkbook wb, ExcelStyle excelStyle) {
		XSSFCellStyle style = wb.createCellStyle();
		//	设置背景色
//		XSSFPalette customPalette = wb.getCustomPalette();
//		HSSFColor similarColor = customPalette.findSimilarColor(Integer.valueOf(255), Integer.valueOf(153), Integer.valueOf(204));
//		XSSFColor similarColor = new XSSFColor(new java.awt.Color(255, 153, 204), new DefaultIndexedColorMap());
//		short index = similarColor.getIndex();
		if (excelStyle != null) {
			int colorR = excelStyle.getColorR();
			int colorG = excelStyle.getColorG();
			int colorB = excelStyle.getColorB();
			style.setFillForegroundColor(new XSSFColor(new java.awt.Color(colorR, colorG, colorB), null));
		}
		style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style.setAlignment(HorizontalAlignment.LEFT); // 居中
		style.setBorderBottom(BorderStyle.THIN); //下边框
		style.setBorderLeft(BorderStyle.THIN);//左边框
		style.setBorderTop(BorderStyle.THIN);//上边框
		style.setBorderRight(BorderStyle.THIN);//右边框
		XSSFFont font = wb.createFont();
		//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontHeightInPoints((short) 11);//设置字体大小
		font.setFontName("宋体");
		//font.setFontHeight((short) 420);
		style.setFont(font);
		return style;
	}
}
